package com.Wu.product.dao;

import com.Wu.Util;
import com.Wu.product.model.Product;

import com.Wu.DBUtil;
import com.Wu.product.model.Product;
import com.Wu.product.model.ShoppingCar;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDao {
    /** 获取商品列表 **/
    public List<Product> getProductList() {
        List<Product> list = new ArrayList<Product>();
//        1.获取数据库链接
        Connection conn = DBUtil.getConnection();
//        2.准备SQL
        String sql = "select * from tb_product order by create_time";
//        3.声明操作对象
        PreparedStatement ps = null;
//        4.声明结果集对象
        ResultSet rs = null;
        try{
//            5.获取操作对象
            ps = conn.prepareStatement(sql);
//            6.执行SQL并返回结果集
            rs = ps.executeQuery();
//            7.解析结果集
            while(rs.next()) {
//                创建商品对象
                Product product = new Product();
                product.setDescription(rs.getString("description"));
                product.setName(rs.getString("name"));
                product.setId(rs.getInt("id"));
                product.setMarket_price(rs.getDouble("market_price"));
                product.setSell_price(rs.getDouble("sell_price"));
                product.setPicture(rs.getString("picture"));
//                将商品信息添加到list中
                list.add(product);
//                将对象置为空，让GC尽快回收相关内存
                product = null;
            }
            return list;
            }catch(
                    SQLException e ){
            throw new RuntimeException(e);
        }

    }
    //根据商品列表获取商品信息
        public Product getProductById (String id){
            Connection conn = DBUtil.getConnection();
            String sql = "select * from product where id = ?";
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = conn.prepareStatement(sql);
                ps.setString(1, id);
                rs = ps.executeQuery();
                if (rs.next()) {
                    Product product = new Product();
                    product.setDescription(rs.getString("description"));
                    product.setName(rs.getString("name"));
                    product.setId(rs.getInt("id"));
                    product.setMarket_price(rs.getDouble("market_price"));
                    product.setSell_price(rs.getDouble("sell_price"));
                    product.setPicture(rs.getString("picture"));
                    return product;
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                DBUtil.closeResource(conn, ps, rs);
            }
            return null;
        }

    /* 将商品添加到购物车 **/
    public int addCar(String customerId,String productId ,String number) {
//1.连接数据库
        Connection conn = DBUtil.getConnection();
        //2.准备SQL
        String sql = "INSERT INTO `tb_car` (`customerid`,`productid`,`number`)VALUES(?,?,?)";
        PreparedStatement ps = null;
        //3.获取操作对象
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, customerId);
            ps.setString(2, productId);
            ps.setString(3, number);
            int result = ps.executeUpdate();
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeResource(conn, ps, null);
        }
    }
//    将商品更新购物车的商品数量
    public int updateCar(String customerId,String productId ,String number) {
        //连接数据库
        Connection conn = DBUtil.getConnection();
//        准备sql
        String sql="update `tb_car` set `number` = ? where customerid = ?and `productid`=?";
        PreparedStatement ps = null;
        //获取操作对象
        try{
            ps= conn.prepareStatement(sql);
            ps.setString(1,number);
            ps.setString(2,customerId);
            ps.setString(3,productId);
            int result=ps.executeUpdate();
            return result;
        }catch(SQLException e ){
            throw new RuntimeException(e);
        }finally {
            DBUtil.closeResource(conn, ps, null);
        }
    }
//    判断此用户是否已填写此商品
    public int isExists(String customerId,String productId) {
        Connection conn = DBUtil.getConnection();
String sql = "select * from tb_car where `customerid` = ? and `productid`=?";
PreparedStatement ps = null;
ResultSet rs = null;
try{
    ps=conn.prepareStatement(sql);
    ps.setString(1,customerId);
    ps.setString(2,productId);
    rs=ps.executeQuery();
    if (rs.next()){
        int number=rs.getInt("number");
        return number;
    }
}catch(SQLException e ){
    throw new RuntimeException(e);
}finally {
    DBUtil.closeResource(conn, ps, rs);
}
return 0;
    }
    //根据用户编号查询这个用户加购了哪些物品，返回购物车列表
    public List<ShoppingCar> getShoppingCarInfo(String customerId) {
        Connection conn = DBUtil.getConnection();
        String sql = "select * from tb_car join tb_product on tb_car.productid=tb_product.id where tb_car.customerid = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<ShoppingCar> list = new ArrayList<>();
        try{
            ps=conn.prepareStatement(sql);
            ps.setString(1,customerId);
            rs=ps.executeQuery();
            while (rs.next()){
                ShoppingCar shoppingCar = new ShoppingCar();
                shoppingCar.setCustomerId(rs.getInt("customerid"));
                shoppingCar.setProductId(rs.getInt("productid"));
                shoppingCar.setDescription(rs.getString("description"));
                shoppingCar.setName(rs.getString("name"));
                shoppingCar.setPicture(rs.getString("picture"));
                shoppingCar.setNumber(rs.getInt("number"));
                shoppingCar.setSell_price(rs.getDouble("sell_price"));
                list.add(shoppingCar);
                shoppingCar = null;
            }
            return list;
        }catch(SQLException e ){
            throw new RuntimeException(e);
        }finally {
            DBUtil.closeResource(conn, ps, rs);
        }
    }

    //根据会员联系方式，及购物车列表来生成订单
    public int addOrder(String phone, String total_price, List<ShoppingCar> list) {
        Connection conn = DBUtil.getConnection();
        String sql = "INSERT INTO `tb_order`(`order_id`,`mobile`,`total_price`)VALUES(?,?,?)";
        PreparedStatement ps = null;
        String orderId = Util.createOrderId();
        int n = 0;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, orderId);
            ps.setString(2, phone);
            ps.setString(3, total_price);
            n = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeResource(null, ps, null);
        }
        if (n > 0) {
            String sql2 = "INSERT INTO `tb_order_item`(`order_id`,`product_id`,`product_name`,`product_price`,`amount`)" + "VALUES(?,?,?,?,?)";
            PreparedStatement ps2 = null;
            try {
                ps2 = conn.prepareStatement(sql2);
                for (int i = 0; i < list.size(); i++) {
                    ShoppingCar shoppingCar = list.get(i);
                    ps2.setString(1, orderId);
                    ps2.setInt(2, shoppingCar.getProductId());
                    ps2.setString(3, shoppingCar.getName());
                    ps2.setDouble(4, shoppingCar.getSell_price());
                    ps2.setInt(5, shoppingCar.getNumber());
                    //添加批处理
                    ps2.addBatch();
                }
                //执行批处理
                ps2.executeBatch();
                return 1;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                DBUtil.closeResource(conn, ps2, null);
            }
        }
        return 0;
    }
}
